const Router = require('koa-router')
const { Product, Customer, Order, OrderItem, Inventory, sequelize, AISQLLog, TableConfig, TableColumn } = require('../models')
const { Op } = require('sequelize')
const auth = require('../middleware/auth')
const axios = require('axios')

const router = new Router({ prefix: '/api/ai' })

// ===== 数据查询执行函数 =====

// 直接通过表格配置API获取结构化数据
async function executeDataQueryWithConfig(queryType, params = {}, userQuery = '', userId = null, sessionId = null) {
  const startTime = Date.now()
  let sqlLogId = null
  
  try {
    // 映射查询类型到表格ID
    const tableIdMap = {
      'products': 'products',
      'customers': 'customers', 
      'orders': 'orders',
      'users': 'users',
      'roles': 'roles',
      'permissions': 'permissions',
      'inventory': 'inventory',
      'ai_sql_logs': 'ai_sql_logs',
      'chat_histories': 'chat_histories',
      'dynamic_menus': 'dynamic_menus'
    }
    
    const tableId = tableIdMap[queryType]
    if (!tableId) {
      return null
    }

    console.log(' 执行表格配置查询:', { queryType, tableId })

    // 获取表格配置
    const tableConfig = await TableConfig.findOne({
      where: { table_id: tableId, status: 'active' },
      include: [
        {
          model: TableColumn,
          as: 'columns',
          where: { status: 'active', visible: true },
          order: [['sort_order', 'ASC']],
          required: false
        }
      ]
    })

    if (!tableConfig) {
      console.log(' 未找到表格配置:', tableId)
      return null
    }

    // 查询表格数据
    const tableName = tableConfig.table_name
    const limit = params.limit || 10
    const offset = 0
    
    // 构建查询
    let dataQuery = `SELECT * FROM ${tableName}`
    
    // 添加排序
    if (tableConfig.sortable_columns && tableConfig.sortable_columns.length > 0) {
      const sortField = tableConfig.sortable_columns[0]
      dataQuery += ` ORDER BY ${sortField} DESC`
    }
    
    dataQuery += ` LIMIT ${limit} OFFSET ${offset}`

    // 创建SQL日志记录
    if (userId) {
      const sqlLog = await AISQLLog.create({
        user_id: userId,
        session_id: sessionId,
        user_query: userQuery,
        sql_statement: dataQuery,
        sql_type: 'SELECT',
        target_table: tableName,
        execution_status: 'pending',
        risk_level: 'low',
        ai_model: 'deepseek-chat'
      })
      sqlLogId = sqlLog.id
    }

    // 获取总数
    const countResult = await sequelize.query(`SELECT COUNT(*) as total FROM ${tableName}`, {
      type: sequelize.QueryTypes.SELECT
    })
    const total = countResult[0]?.total || 0

    // 获取数据
    console.log(' 执行SQL查询:', dataQuery)
    const data = await sequelize.query(dataQuery, {
      type: sequelize.QueryTypes.SELECT
    })

    console.log(' 查询结果:', { total, dataCount: data.length, firstRow: data[0] })

    // 更新SQL日志为成功状态
    if (sqlLogId) {
      const executionTime = Date.now() - startTime
      await AISQLLog.update({
        execution_status: 'success',
        execution_time: executionTime,
        rows_affected: data.length,
        execution_result: { total, dataCount: data.length },
        executed_at: new Date()
      }, {
        where: { id: sqlLogId }
      })
    }

    // 返回结构化数据，包含SQL语句
    return {
      type: 'table',
      data: {
        title: tableConfig.name,
        description: tableConfig.description,
        table_id: tableConfig.table_id,
        columns: tableConfig.columns.map(col => ({
          key: col.key,
          title: col.title,
          width: col.width,
          sortable: col.sortable,
          searchable: col.searchable,
          value_type: col.value_type,
          render_type: col.render_type,
          fixed: col.fixed
        })),
        rows: data,
        pagination: {
          current: 1,
          pageSize: limit,
          total: total,
          pages: Math.ceil(total / limit)
        },
        config: {
          searchable_columns: tableConfig.searchable_columns,
          sortable_columns: tableConfig.sortable_columns,
          filterable_columns: tableConfig.filterable_columns,
          export_enabled: tableConfig.export_enabled,
          batch_operations: tableConfig.batch_operations
        }
      },
      summary: `已查询${tableConfig.name}，共${total}条记录，显示前${data.length}条`,
      // 添加SQL语句信息
      sqlStatements: [{
        content: dataQuery,
        type: 'SELECT',
        info: '查询语句 - 用于检索数据',
        riskLevel: 'low'
      }]
    }
  } catch (error) {
    console.error('执行表格配置查询失败:', error)
    
    // 更新SQL日志为失败状态
    if (sqlLogId) {
      const executionTime = Date.now() - startTime
      await AISQLLog.update({
        execution_status: 'failed',
        execution_time: executionTime,
        error_message: error.message,
        executed_at: new Date()
      }, {
        where: { id: sqlLogId }
      }).catch(logError => {
        console.error('更新SQL日志失败:', logError)
      })
    }
    
    return null
  }
}

// ===== 数据结构化解析函数 =====

// 基于表格配置的业务数据解析
async function parseBusinessDataWithConfig(queryType, dataContext) {
  try {
    // 获取对应的表格配置
    const tableIdMap = {
      'products': 'products',
      'customers': 'customers', 
      'orders': 'orders',
      'inventory': 'inventory'
    }
    
    const tableId = tableIdMap[queryType]
    if (!tableId) {
      return parseBusinessData(queryType, dataContext) // 降级到原有方法
    }

    // 获取表格配置和列配置
    const tableConfig = await TableConfig.findOne({
      where: { table_id: tableId, status: 'active' },
      include: [
        {
          model: TableColumn,
          as: 'columns',
          where: { status: 'active', visible: true },
          order: [['sort_order', 'ASC']],
          required: false
        }
      ]
    })

    if (!tableConfig || !tableConfig.columns) {
      return parseBusinessData(queryType, dataContext) // 降级到原有方法
    }

    // 解析数据上下文中的表格数据
    const lines = dataContext.split('\n').filter(line => line.trim())
    if (lines.length < 3) {
      return parseBusinessData(queryType, dataContext) // 降级到原有方法
    }

    // 找到表格数据
    const headerIndex = lines.findIndex(line => line.includes('|') && line.includes('---'))
    if (headerIndex === -1) {
      return parseBusinessData(queryType, dataContext) // 降级到原有方法
    }

    const headerLine = lines[headerIndex - 1]
    const dataLines = lines.slice(headerIndex + 1)

    // 解析表头
    const headers = headerLine.split('|').map(h => h.trim()).filter(h => h)
    
    // 解析数据行
    const rows = dataLines
      .filter(line => line.includes('|'))
      .map(line => {
        const cells = line.split('|').map(c => c.trim()).filter(c => c)
        const row = {}
        headers.forEach((header, index) => {
          row[header] = cells[index] || ''
        })
        return row
      })

    return {
      type: 'table',
      data: {
        title: tableConfig.name,
        description: tableConfig.description,
        table_id: tableConfig.table_id,
        columns: tableConfig.columns.map(col => ({
          key: col.key,
          title: col.title,
          width: col.width,
          sortable: col.sortable,
          searchable: col.searchable,
          value_type: col.value_type,
          render_type: col.render_type
        })),
        rows: rows,
        pagination: {
          current: 1,
          pageSize: rows.length,
          total: rows.length
        },
        config: {
          searchable_columns: tableConfig.searchable_columns,
          sortable_columns: tableConfig.sortable_columns,
          export_enabled: tableConfig.export_enabled
        }
      }
    }
  } catch (error) {
    console.error('基于配置的数据解析失败:', error)
    return parseBusinessData(queryType, dataContext) // 降级到原有方法
  }
}

async function parseDataContextToStructured(queryType, dataContext, userMessage) {
  try {
    switch (queryType) {
      case 'tables':
        return {
          type: 'table',
          data: {
            title: '数据库表列表',
            columns: [
              { key: 'table_name', title: '表名', type: 'string' },
              { key: 'table_comment', title: '表注释', type: 'string' },
              { key: 'table_rows', title: '记录数', type: 'number' }
            ],
            rows: parseTablesFromMarkdown(dataContext)
          }
        }
        
      case 'table_structure':
        return parseTableStructureData(dataContext)
        
      case 'orders':
      case 'products':
      case 'customers':
      case 'inventory':
        return await parseBusinessDataWithConfig(queryType, dataContext)
        
      case 'create_table':
        return {
          type: 'action_result',
          data: {
            action: 'create_table',
            status: 'success',
            message: 'SQL语句已生成，请确认是否执行',
            sql: dataContext
          }
        }
        
      default:
        return {
          type: 'text',
          data: { message: dataContext }
        }
    }
  } catch (error) {
    console.error('解析结构化数据失败:', error)
    return {
      type: 'text',
      data: { message: dataContext }
    }
  }
}

function parseTablesFromMarkdown(markdownText) {
  const lines = markdownText.split('\n')
  const rows = []
  
  lines.forEach(line => {
    if (line.includes('|') && !line.includes('---')) {
      const cells = line.split('|').map(cell => cell.trim()).filter(cell => cell)
      if (cells.length >= 2 && cells[0] !== '表名') {
        rows.push({
          table_name: cells[0] || '',
          table_comment: cells[1] || '',
          table_rows: parseInt(cells[2]) || 0
        })
      }
    }
  })
  
  return rows
}

function parseTableStructureData(dataContext) {
  const lines = dataContext.split('\n')
  let tableName = ''
  let columns = []
  let sampleData = []
  
  const tableMatch = dataContext.match(/表名：(\w+)/)
  if (tableMatch) {
    tableName = tableMatch[1]
  }
  
  let inFieldsSection = false
  let inDataSection = false
  
  lines.forEach(line => {
    if (line.includes('字段信息')) {
      inFieldsSection = true
      return
    }
    if (line.includes('示例数据')) {
      inFieldsSection = false
      inDataSection = true
      return
    }
    
    if (inFieldsSection && line.includes('|')) {
      const cells = line.split('|').map(cell => cell.trim()).filter(cell => cell)
      if (cells.length >= 3 && cells[0] !== '字段名') {
        columns.push({
          key: cells[0],
          title: cells[2] || cells[0],
          type: mapSqlTypeToDisplayType(cells[1])
        })
      }
    }
    
    if (inDataSection && line.includes('|')) {
      const cells = line.split('|').map(cell => cell.trim()).filter(cell => cell)
      if (cells.length > 0 && cells[0] !== '字段名') {
        const rowData = {}
        columns.forEach((col, index) => {
          rowData[col.key] = cells[index] || ''
        })
        sampleData.push(rowData)
      }
    }
  })
  
  return {
    type: 'table',
    data: {
      title: `${tableName} 表数据`,
      tableName: tableName,
      columns: columns,
      rows: sampleData
    }
  }
}

function parseBusinessData(queryType, dataContext) {
  const typeMap = {
    orders: '订单',
    products: '产品',
    customers: '客户', 
    inventory: '库存'
  }
  
  return {
    type: 'table',
    data: {
      title: `${typeMap[queryType]}数据`,
      columns: [],
      rows: []
    }
  }
}

function mapSqlTypeToDisplayType(sqlType) {
  if (sqlType.includes('int') || sqlType.includes('decimal') || sqlType.includes('float')) {
    return 'number'
  }
  if (sqlType.includes('date') || sqlType.includes('time')) {
    return 'date'
  }
  return 'string'
}

// DeepSeek API配置
const DEEPSEEK_API_URL = 'https://api.deepseek.com/v1/chat/completions'
const DEEPSEEK_API_KEY = process.env.DEEPSEEK_API_KEY || 'sk-0b620cb2a4014b0a97c80ed6f2e98928'

// DeepSeek模型配置 - 使用最强的模型
const DEEPSEEK_MODEL = 'deepseek-chat' // 最新最强的对话模型
const DEEPSEEK_CONFIG = {
  model: DEEPSEEK_MODEL,
  temperature: 0.7,        // 适中的创造性
  max_tokens: 4000,        // 充足的回复长度
  top_p: 0.9,             // 高质量回复
  frequency_penalty: 0.1,  // 减少重复
  presence_penalty: 0.1,   // 增加话题多样性
  stream: false           // 完整响应
}

console.log('✅ DeepSeek API已配置，使用模型:', DEEPSEEK_MODEL)

// AI聊天对话接口
router.post('/chat', auth.checkPermission('chat:write'), async (ctx) => {
  console.log(' AI Chat API调用开始:', { message: ctx.request.body.message?.substring(0, 50) + '...' })
  
  try {
    const { message, context = [], session_id } = ctx.request.body

    // 验证输入
    if (!message || typeof message !== 'string') {
      console.log(' 输入验证失败:', { message, type: typeof message })
      ctx.status = 400
      ctx.body = {
        success: false,
        message: '消息内容不能为空'
      }
      return
    }

    // AI应该直接通过SQL处理菜单控制，不需要单独的判断逻辑

    // 检查是否需要数据查询
    const needsDataQuery = await checkIfNeedsDataQuery(message)
    let dataContext = ''

    // 如果需要查询数据，直接返回结构化数据，不调用AI
    if (needsDataQuery.needsQuery) {
      try {
        const userId = ctx.state.user?.userId
        const structuredResult = await executeDataQueryWithConfig(needsDataQuery.queryType, needsDataQuery.params, message, userId, session_id)
        if (structuredResult) {
          console.log('📊 直接返回结构化数据:', { type: structuredResult.type })
          ctx.body = {
            success: true,
            message: 'AI执行成功',
            data: structuredResult
          }
          return
        }
      } catch (error) {
        console.error('数据查询失败:', error)
        // 继续执行AI对话作为降级方案
      }
    }

    // 构建系统提示词 - 包含数据上下文
    let systemPrompt = `你是X-Synergy ERP系统的AI管家，可以直接执行SQL操作：

**菜单控制规则：当用户要求修改菜单显示时，你必须仔细理解用户意图：**

1. 先查看当前状态：
\`\`\`sql
SELECT name, \`key\`, status FROM dynamic_menus ORDER BY sort_order;
\`\`\`

2. 根据用户要求更新菜单状态：
- 如果用户说"只显示X、Y、Z"，则只有X、Y、Z设为active，其他设为inactive
- 如果用户说"不用显示X"或"隐藏X"，则只有X设为inactive，其他保持不变
- 如果用户说"显示X"，则只有X设为active，其他保持不变

\`\`\`sql
UPDATE dynamic_menus SET status = CASE 
  WHEN \`key\` = 'specific_key' THEN 'inactive'  -- 针对性隐藏
  ELSE status  -- 其他保持不变
END;
\`\`\`

**菜单key对应关系：**
- 用户管理: users
- 角色管理: roles  
- 权限管理: permissions
- 客户管理: customers
- 产品管理: products
- 产品分类: product_categories
- 订单管理: orders
- 库存管理: inventory

**重要：**
- 仔细理解用户意图，不要过度修改
- 必须使用SQL代码块格式：\`\`\`sql ... \`\`\`
- 每次操作只生成2条SQL：1条查询 + 1条更新
- 回复要简洁，不要重复内容

请简洁回复，**对于任何涉及数据操作的请求，必须生成并执行相应的SQL语句**。`

    // 如果有数据上下文，添加到系统提示词中
    if (dataContext) {
      systemPrompt += `\n\n当前数据上下文：\n${dataContext}`
    }

    // 准备消息历史，确保所有消息都有content字段
    const messages = [
      { role: 'system', content: systemPrompt },
      // 过滤和验证context中的消息
      ...context.filter(msg => msg && msg.role && msg.content && typeof msg.content === 'string'),
      { role: 'user', content: message }
    ]
    
    console.log('消息验证:', messages.map(msg => ({ role: msg.role, hasContent: !!msg.content, contentLength: msg.content?.length })))

    console.log(' 准备调用DeepSeek API:', { 
      messageCount: messages.length, 
      model: DEEPSEEK_CONFIG.model,
      apiUrl: DEEPSEEK_API_URL 
    })

    // 调用DeepSeek API (优化配置减少超时)
    const apiConfig = {
      ...DEEPSEEK_CONFIG,
      max_tokens: 2000, // 减少token长度
      temperature: 0.5,  // 降低创造性提高速度
      messages: messages
    }

    const response = await axios.post(DEEPSEEK_API_URL, apiConfig, {
      headers: {
        'Authorization': `Bearer ${DEEPSEEK_API_KEY || 'sk-0b620cb2a4014b0a97c80ed6f2e98928'}`,
        'Content-Type': 'application/json'
      },
      timeout: 25000 // 减少到25秒
    })

    console.log('📥 DeepSeek API响应成功:', { 
      status: response.status, 
      hasChoices: !!response.data.choices,
      choicesLength: response.data.choices?.length 
    })

    let aiResponse = response.data.choices[0].message.content
    const usage = response.data.usage || {
      prompt_tokens: 0,
      completion_tokens: 0,
      total_tokens: 0
    }

    console.log('AI对话成功，响应长度:', aiResponse.length)

    // 检查AI回复中是否包含SQL语句，如果有则执行
    const userId = ctx.state.user?.userId
    
    // 扩大SQL检测范围，包含更多可能的SQL操作
    const containsSQL = aiResponse.includes('UPDATE') || 
                       aiResponse.includes('SELECT') || 
                       aiResponse.includes('INSERT') ||
                       aiResponse.includes('DELETE') ||
                       aiResponse.includes('```sql') ||
                       aiResponse.includes('dynamic_menus') ||
                       aiResponse.includes('菜单') && (aiResponse.includes('设置') || aiResponse.includes('显示') || aiResponse.includes('隐藏'))
    
     if (containsSQL) {
       try {
         console.log('🔍 检测到可能的SQL操作，尝试执行...', { 
           hasUpdate: aiResponse.includes('UPDATE'),
           hasSelect: aiResponse.includes('SELECT'),
           hasMenuKeyword: aiResponse.includes('dynamic_menus'),
           responseLength: aiResponse.length
         })
         
         // 执行SQL并获取执行结果
         const sqlExecutionResult = await executeMenuControlSQL(aiResponse, userId, session_id, message)
         if (sqlExecutionResult) {
           console.log('✅ SQL执行成功，更新AI回复内容')
           // 将执行结果追加到原始AI回复中，而不是替换
           aiResponse = aiResponse + '\n\n' + sqlExecutionResult
         } else {
           console.log('⚠️ SQL执行返回空结果')
         }
       } catch (error) {
         console.error('执行SQL失败:', error)
         aiResponse += `\n\n⚠️ SQL执行失败: ${error.message}`
       }
     } else {
       console.log('🔍 未检测到SQL操作，返回纯文本回复', {
         containsUpdate: aiResponse.includes('UPDATE'),
         containsSelect: aiResponse.includes('SELECT'),
         containsMenu: aiResponse.includes('菜单'),
         responsePreview: aiResponse.substring(0, 200) + '...'
       })
     }

    // 检查AI响应中是否包含SQL语句，如果有则提取并作为结构化数据返回
    const sqlData = extractSQLFromResponse(aiResponse)
    
    // 检查是否需要返回结构化数据
    let responseData = {
      type: sqlData.hasSql ? 'sql' : 'text', // text | table | chart | action_result | sql
      message: aiResponse,
      sqlStatements: sqlData.statements, // 添加SQL语句数组
      usage: usage,
      context: [...context, 
        { role: 'user', content: message },
        { role: 'assistant', content: aiResponse }
      ].slice(-10)
    }

    // 如果有数据查询结果，尝试解析为结构化数据
    if (needsDataQuery.needsQuery && dataContext) {
      try {
        const structuredData = await parseDataContextToStructured(needsDataQuery.queryType, dataContext, message)
        if (structuredData) {
          responseData = {
            ...responseData,
            ...structuredData
          }
          console.log('📊 结构化数据处理完成:', { type: structuredData.type })
        }
      } catch (error) {
        console.error('结构化数据处理失败:', error)
      }
    }

    ctx.body = {
      success: true,
      message: 'AI执行成功',
      data: responseData
    }
  } catch (error) {
    console.error('AI对话失败 - 详细错误信息:')
    console.error('错误类型:', error.name)
    console.error('错误消息:', error.message)
    console.error('错误代码:', error.code)
    console.error('错误堆栈:', error.stack)
    
    if (error.response) {
      console.error('HTTP响应状态:', error.response.status)
      console.error('HTTP响应数据:', error.response.data)
    }
    
    let errorMessage = 'AI服务暂时不可用，请稍后重试'
    if (error.response?.status === 401) {
      errorMessage = 'API密钥无效，请检查配置'
    } else if (error.response?.status === 429) {
      errorMessage = 'API调用频率过高，请稍后重试'
    } else if (error.code === 'ECONNABORTED' || error.code === 'ETIMEDOUT') {
      errorMessage = '请求超时，请简化问题或稍后重试'
    } else if (error.code === 'ECONNRESET') {
      errorMessage = '网络连接中断，请重新尝试或简化查询内容'
    }
    
    ctx.status = 500
    ctx.body = {
      success: false,
      message: errorMessage,
      data: null
    }
  }
})

// 智能数据查询接口
router.post('/query', auth.checkPermission('chat:read'), async (ctx) => {
  try {
    const { query, type = 'auto' } = ctx.request.body

    if (!query) {
      ctx.status = 400
      ctx.body = {
        code: 400,
        message: '查询内容不能为空'
      }
      return
    }

    let results = {}
    const lowerQuery = query.toLowerCase()

    // 根据查询类型和关键词智能判断查询内容
    if (type === 'auto' || type === 'product') {
      // 产品相关查询
      if (lowerQuery.includes('产品') || lowerQuery.includes('商品') || lowerQuery.includes('库存')) {
        const products = await Product.findAll({
          include: [
            {
              model: Inventory,
              as: 'inventory',
              attributes: ['quantity', 'reserved_quantity', 'min_stock']
            }
          ],
          limit: 10,
          order: [['created_at', 'DESC']]
        })
        results.products = products
      }
    }

    if (type === 'auto' || type === 'customer') {
      // 客户相关查询
      if (lowerQuery.includes('客户') || lowerQuery.includes('用户')) {
        const customers = await Customer.findAll({
          limit: 10,
          order: [['created_at', 'DESC']]
        })
        results.customers = customers
      }
    }

    if (type === 'auto' || type === 'order') {
      // 订单相关查询
      if (lowerQuery.includes('订单') || lowerQuery.includes('销售')) {
        const orders = await Order.findAll({
          include: [
            {
              model: Customer,
              as: 'customer',
              attributes: ['id', 'name']
            }
          ],
          limit: 10,
          order: [['created_at', 'DESC']]
        })
        results.orders = orders
      }
    }

    // 统计数据
    const stats = {
      totalProducts: await Product.count(),
      totalCustomers: await Customer.count(),
      totalOrders: await Order.count(),
      pendingOrders: await Order.count({ where: { status: 'pending' } })
    }
    results.stats = stats

    // 如果没有找到相关数据，提供建议
    if (Object.keys(results).length === 1) { // 只有stats
      results.suggestion = '没有找到相关数据，您可以尝试查询：产品信息、客户列表、订单状态、库存情况等'
    }

    ctx.body = {
      code: 200,
      message: '数据查询成功',
      data: results
    }
  } catch (error) {
    console.error('智能查询失败:', error)
    ctx.body = {
      code: 500,
      message: '数据查询失败',
      error: error.message
    }
  }
})

// 业务分析接口
router.post('/analyze', auth.checkPermission('system:manage'), async (ctx) => {
  try {
    const { type, dateRange } = ctx.request.body

    let analysis = {}
    const now = new Date()
    const startDate = dateRange?.start ? new Date(dateRange.start) : new Date(now.getFullYear(), now.getMonth(), 1)
    const endDate = dateRange?.end ? new Date(dateRange.end) : now

    if (type === 'sales' || type === 'all') {
      // 销售分析
      const salesData = await Order.findAll({
        where: {
          created_at: {
            [sequelize.Sequelize.Op.between]: [startDate, endDate]
          }
        },
        attributes: [
          [sequelize.fn('DATE', sequelize.col('created_at')), 'date'],
          [sequelize.fn('COUNT', sequelize.col('id')), 'orderCount'],
          [sequelize.fn('SUM', sequelize.col('total_amount')), 'totalAmount']
        ],
        group: [sequelize.fn('DATE', sequelize.col('created_at'))],
        order: [[sequelize.fn('DATE', sequelize.col('created_at')), 'ASC']],
        raw: true
      })
      analysis.sales = salesData
    }

    if (type === 'inventory' || type === 'all') {
      // 库存分析
      const lowStockProducts = await Inventory.findAll({
        where: {
          [sequelize.Sequelize.Op.and]: [
            sequelize.where(
              sequelize.col('quantity'),
              sequelize.Sequelize.Op.lte,
              sequelize.col('min_stock')
            )
          ]
        },
        include: [
          {
            model: Product,
            as: 'product',
            attributes: ['name', 'sku']
          }
        ],
        limit: 10
      })
      analysis.lowStock = lowStockProducts
    }

    if (type === 'customer' || type === 'all') {
      // 客户分析
      const topCustomers = await Customer.findAll({
        include: [
          {
            model: Order,
            as: 'orders',
            attributes: [],
            where: {
              created_at: {
                [sequelize.Sequelize.Op.between]: [startDate, endDate]
              }
            },
            required: false
          }
        ],
        attributes: [
          'id', 'name',
          [sequelize.fn('COUNT', sequelize.col('orders.id')), 'orderCount'],
          [sequelize.fn('SUM', sequelize.col('orders.total_amount')), 'totalSpent']
        ],
        group: ['Customer.id'],
        order: [[sequelize.fn('SUM', sequelize.col('orders.total_amount')), 'DESC']],
        limit: 10,
        subQuery: false
      })
      analysis.topCustomers = topCustomers
    }

    ctx.body = {
      code: 200,
      message: '业务分析完成',
      data: analysis
    }
  } catch (error) {
    console.error('业务分析失败:', error)
    ctx.body = {
      code: 500,
      message: '业务分析失败',
      error: error.message
    }
  }
})

// 删除多余的菜单控制接口 - AI应该直接执行SQL

// AI建议接口
router.post('/suggest', async (ctx) => {
  try {
    const { context, data } = ctx.request.body

    // 获取业务数据作为上下文
    const businessContext = {
      totalProducts: await Product.count(),
      totalCustomers: await Customer.count(),
      pendingOrders: await Order.count({ where: { status: 'pending' } }),
      lowStockCount: await Inventory.count({
        where: {
          [sequelize.Sequelize.Op.and]: [
            sequelize.where(
              sequelize.col('current_stock'),
              sequelize.Sequelize.Op.lte,
              sequelize.col('min_stock')
            )
          ]
        }
      })
    }

    const prompt = `基于以下业务数据，请提供专业的业务建议：

业务概况：
- 产品总数：${businessContext.totalProducts}
- 客户总数：${businessContext.totalCustomers}
- 待处理订单：${businessContext.pendingOrders}
- 低库存产品：${businessContext.lowStockCount}

用户请求：${context || '请提供整体业务优化建议'}

请提供具体、可执行的建议。`

    // 调用DeepSeek API生成建议
    const response = await axios.post(DEEPSEEK_API_URL, {
      model: 'deepseek-chat',
      messages: [
        {
          role: 'system',
          content: '你是一个专业的ERP业务顾问，擅长分析业务数据并提供优化建议。'
        },
        {
          role: 'user',
          content: prompt
        }
      ],
      max_tokens: 1000,
      temperature: 0.8
    }, {
      headers: {
        'Authorization': `Bearer ${DEEPSEEK_API_KEY || 'sk-0b620cb2a4014b0a97c80ed6f2e98928'}`,
        'Content-Type': 'application/json'
      },
      timeout: 30000
    })

    const suggestions = response.data.choices[0].message.content

    ctx.body = {
      code: 200,
      message: 'AI建议生成成功',
      data: {
        suggestions,
        businessContext
      }
    }
  } catch (error) {
    console.error('AI建议生成失败:', error)
    ctx.body = {
      code: 500,
      message: 'AI建议服务暂时不可用',
      error: error.message
    }
  }
})

// 数据查询辅助函数
async function checkIfNeedsDataQuery(message) {
  const lowerMessage = message.toLowerCase()
  
  // 优先检查菜单控制操作（避免被数据查询误判）
  if ((lowerMessage.includes('修改') || lowerMessage.includes('设置') || lowerMessage.includes('调整')) && 
      (lowerMessage.includes('菜单') || lowerMessage.includes('界面') || lowerMessage.includes('显示') || lowerMessage.includes('隐藏'))) {
    // 这是菜单控制操作，不是数据查询，让AI直接处理
    return { needsQuery: false }
  }
  
  // 检查菜单管理操作
  if (lowerMessage.includes('加个菜单') || lowerMessage.includes('添加菜单') || lowerMessage.includes('新增菜单')) {
    return {
      needsQuery: true,
      queryType: 'add_menu',
      params: { request: message }
    }
  }
  
  if ((lowerMessage.includes('删除菜单') || lowerMessage.includes('去掉')) && lowerMessage.includes('菜单')) {
    return {
      needsQuery: true,
      queryType: 'remove_menu', 
      params: { request: message }
    }
  }
  
  // 检查是否需要查看所有表格
  if (lowerMessage.includes('多少张表') || lowerMessage.includes('所有表') || lowerMessage.includes('表格列表') || lowerMessage.includes('数据表')) {
    return {
      needsQuery: true,
      queryType: 'tables',
      params: {}
    }
  }
  
  // 检查是否需要查询特定表的结构
  const tableStructureMatch = lowerMessage.match(/查看\s*(\w+)\s*表/)
  if (tableStructureMatch) {
    return {
      needsQuery: true,
      queryType: 'table_structure',
      params: { tableName: tableStructureMatch[1] }
    }
  }
  
  // 检查是否需要查询订单数据
  if (lowerMessage.includes('订单') || lowerMessage.includes('销售') || lowerMessage.includes('order')) {
    return {
      needsQuery: true,
      queryType: 'orders',
      params: { limit: 20 }
    }
  }
  
  // 检查是否需要查询产品数据（排除菜单设置相关的请求）
  if ((lowerMessage.includes('产品') || lowerMessage.includes('商品') || lowerMessage.includes('product')) &&
      !lowerMessage.includes('菜单') && !lowerMessage.includes('显示') && !lowerMessage.includes('隐藏') && 
      !lowerMessage.includes('设置') && !lowerMessage.includes('不用') && !lowerMessage.includes('去掉')) {
    return {
      needsQuery: true,
      queryType: 'products',
      params: { limit: 20 }
    }
  }
  
  // 检查是否需要查询用户数据
  if (lowerMessage.includes('用户') || lowerMessage.includes('用户列表') || lowerMessage.includes('user')) {
    return {
      needsQuery: true,
      queryType: 'users',
      params: { limit: 20 }
    }
  }
  
  // 检查是否需要查询角色数据
  if (lowerMessage.includes('角色') || lowerMessage.includes('角色管理') || lowerMessage.includes('role')) {
    return {
      needsQuery: true,
      queryType: 'roles',
      params: { limit: 20 }
    }
  }
  
  // 检查是否需要查询权限数据
  if (lowerMessage.includes('权限') || lowerMessage.includes('权限管理') || lowerMessage.includes('permission')) {
    return {
      needsQuery: true,
      queryType: 'permissions',
      params: { limit: 50 }
    }
  }
  
  // 检查是否需要查询库存数据
  if (lowerMessage.includes('库存') || lowerMessage.includes('库存管理') || lowerMessage.includes('inventory')) {
    return {
      needsQuery: true,
      queryType: 'inventory',
      params: { limit: 20 }
    }
  }
  
  // 检查是否需要查询SQL日志
  if (lowerMessage.includes('sql日志') || lowerMessage.includes('ai日志') || lowerMessage.includes('执行日志')) {
    return {
      needsQuery: true,
      queryType: 'ai_sql_logs',
      params: { limit: 20 }
    }
  }
  
  // 检查是否需要查询聊天记录
  if (lowerMessage.includes('聊天记录') || lowerMessage.includes('对话历史') || lowerMessage.includes('chat')) {
    return {
      needsQuery: true,
      queryType: 'chat_histories',
      params: { limit: 20 }
    }
  }
  
  // 检查是否需要查询动态菜单（仅限于查看菜单列表，不包括设置操作）
  if ((lowerMessage.includes('查看') || lowerMessage.includes('显示')) && 
      (lowerMessage.includes('动态菜单') || lowerMessage.includes('菜单列表') || lowerMessage.includes('所有菜单')) &&
      !lowerMessage.includes('设置') && !lowerMessage.includes('只显示') && !lowerMessage.includes('隐藏') && !lowerMessage.includes('配置')) {
    return {
      needsQuery: true,
      queryType: 'dynamic_menus',
      params: { limit: 20 }
    }
  }
  
  // 检查是否需要执行SQL
  if (lowerMessage.includes('执行sql') || lowerMessage.includes('运行sql') || lowerMessage.includes('sql查询')) {
    return {
      needsQuery: true,
      queryType: 'execute_sql',
      params: { request: message }
    }
  }
  
  // 检查是否需要查询客户数据
  if (lowerMessage.includes('客户') || lowerMessage.includes('顾客') || lowerMessage.includes('customer')) {
    return {
      needsQuery: true,
      queryType: 'customers',
      params: { limit: 20 }
    }
  }
  
  // 检查是否需要查询库存数据
  if (lowerMessage.includes('库存') || lowerMessage.includes('inventory') || lowerMessage.includes('stock')) {
    return {
      needsQuery: true,
      queryType: 'inventory',
      params: {}
    }
  }
  
  // 检查是否需要创建表格
  if (lowerMessage.includes('创建') && lowerMessage.includes('表')) {
    return {
      needsQuery: true,
      queryType: 'create_table',
      params: { request: message }
    }
  }
  
  return { needsQuery: false }
}

async function queryBusinessData(queryType, params) {
  try {
    switch (queryType) {
      case 'tables':
        return await queryAllTables()
      case 'table_structure':
        return await queryTableStructure(params.tableName)
      case 'orders':
        return await queryOrdersData(params)
      case 'products':
        return await queryProductsData(params)
      case 'customers':
        return await queryCustomersData(params)
      case 'inventory':
        return await queryInventoryData(params)
      case 'create_table':
        return await handleCreateTable(params.request)
        
      case 'add_menu':
        return await handleAddMenu(params.request)
        
      case 'remove_menu':
        return await handleRemoveMenu(params.request)
        
      case 'execute_sql':
        return await handleExecuteSQL(params.request, params.sessionId, params.userId)
        
      default:
        return ''
    }
  } catch (error) {
    console.error('查询业务数据失败:', error)
    return `查询失败: ${error.message}`
  }
}

async function queryOrdersData(params) {
  const orders = await Order.findAll({
    limit: 10,
    order: [['created_at', 'DESC']],
    include: [
      {
        model: Customer,
        as: 'customer',
        attributes: ['name', 'email', 'phone']
      },
      {
        model: OrderItem,
        as: 'items',
        include: [{
          model: Product,
          as: 'product',
          attributes: ['name', 'price']
        }]
      }
    ]
  })

  if (orders.length === 0) {
    return '暂无订单数据'
  }

  let dataText = '最近10条销售订单数据：\n\n'
  dataText += '| 订单号 | 客户名称 | 订单状态 | 订单金额 | 创建时间 | 商品信息 |\n'
  dataText += '|--------|----------|----------|----------|----------|----------|\n'

  orders.forEach(order => {
    const customer = order.customer?.name || '未知客户'
    const amount = order.total_amount || 0
    const status = getOrderStatusText(order.status)
    const date = new Date(order.created_at).toLocaleDateString('zh-CN')
    const items = order.items?.map(item => 
      `${item.product?.name || '未知商品'}(${item.quantity}件)`
    ).join(', ') || '无商品'
    
    dataText += `| ${order.order_number || order.id} | ${customer} | ${status} | ¥${amount} | ${date} | ${items} |\n`
  })

  return dataText
}

async function queryProductsData(params) {
  const products = await Product.findAll({
    limit: 10,
    order: [['created_at', 'DESC']],
    include: [{
      model: ProductCategory,
      as: 'category',
      attributes: ['name']
    }]
  })

  if (products.length === 0) {
    return '暂无产品数据'
  }

  let dataText = '最近10条产品数据：\n\n'
  dataText += '| 产品ID | 产品名称 | 分类 | 价格 | 库存 | 状态 | 创建时间 |\n'
  dataText += '|--------|----------|------|------|------|------|----------|\n'

  products.forEach(product => {
    const category = product.category?.name || '未分类'
    const price = product.price || 0
    const stock = product.stock_quantity || 0
    const status = product.status === 1 ? '上架' : '下架'
    const date = new Date(product.created_at).toLocaleDateString('zh-CN')
    
    dataText += `| ${product.id} | ${product.name} | ${category} | ¥${price} | ${stock} | ${status} | ${date} |\n`
  })

  return dataText
}

async function queryCustomersData(params) {
  const customers = await Customer.findAll({
    limit: 10,
    order: [['created_at', 'DESC']]
  })

  if (customers.length === 0) {
    return '暂无客户数据'
  }

  let dataText = '最近10条客户数据：\n\n'
  dataText += '| 客户ID | 客户名称 | 邮箱 | 电话 | 地址 | 注册时间 |\n'
  dataText += '|--------|----------|------|------|------|----------|\n'

  customers.forEach(customer => {
    const email = customer.email || '未填写'
    const phone = customer.phone || '未填写'
    const address = customer.address || '未填写'
    const date = new Date(customer.created_at).toLocaleDateString('zh-CN')
    
    dataText += `| ${customer.id} | ${customer.name} | ${email} | ${phone} | ${address} | ${date} |\n`
  })

  return dataText
}

async function queryInventoryData(params) {
  const inventory = await Inventory.findAll({
    limit: 10,
    order: [['updated_at', 'DESC']],
    include: [{
      model: Product,
      as: 'product',
      attributes: ['name', 'sku']
    }]
  })

  if (inventory.length === 0) {
    return '暂无库存数据'
  }

  let dataText = '最近10条库存数据：\n\n'
  dataText += '| 产品名称 | SKU | 当前库存 | 最小库存 | 最大库存 | 状态 | 更新时间 |\n'
  dataText += '|----------|-----|----------|----------|----------|------|----------|\n'

  inventory.forEach(item => {
    const productName = item.product?.name || '未知产品'
    const sku = item.product?.sku || '无SKU'
    const current = item.current_stock || 0
    const min = item.min_stock || 0
    const max = item.max_stock || 0
    const status = current <= min ? '库存不足' : current >= max ? '库存充足' : '正常'
    const date = new Date(item.updated_at).toLocaleDateString('zh-CN')
    
    dataText += `| ${productName} | ${sku} | ${current} | ${min} | ${max} | ${status} | ${date} |\n`
  })

  return dataText
}

function getOrderStatusText(status) {
  const statusMap = {
    'pending': '待处理',
    'confirmed': '已确认',
    'processing': '处理中',
    'shipped': '已发货',
    'delivered': '已送达',
    'cancelled': '已取消',
    'refunded': '已退款'
  }
  return statusMap[status] || '未知状态'
}

// 查询所有数据表
async function queryAllTables() {
  try {
    const [results] = await sequelize.query(`
      SELECT TABLE_NAME, TABLE_COMMENT, TABLE_ROWS, 
             ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS 'SIZE_MB'
      FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_SCHEMA = DATABASE()
      ORDER BY TABLE_NAME
    `)

    let dataText = '当前数据库中的所有表格：\n\n'
    dataText += '| 表名 | 说明 | 记录数 | 大小(MB) |\n'
    dataText += '|------|------|--------|----------|\n'

    results.forEach(table => {
      const name = table.TABLE_NAME
      const comment = table.TABLE_COMMENT || '无说明'
      const rows = table.TABLE_ROWS || 0
      const size = table.SIZE_MB || 0
      
      dataText += `| ${name} | ${comment} | ${rows} | ${size} |\n`
    })

    dataText += `\n共找到 ${results.length} 张数据表。`
    return dataText

  } catch (error) {
    console.error('查询表格列表失败:', error)
    return '查询表格列表失败'
  }
}

// 查询特定表的结构
async function queryTableStructure(tableName) {
  try {
    // 查询表结构
    const [columns] = await sequelize.query(`
      SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?
      ORDER BY ORDINAL_POSITION
    `, { replacements: [tableName] })

    if (columns.length === 0) {
      return `表 "${tableName}" 不存在`
    }

    // 查询前10条数据（减少数据量）
    const [data] = await sequelize.query(`SELECT * FROM ${tableName} LIMIT 10`)

    let result = `表 "${tableName}" 的结构和数据：\n\n`
    
    // 表结构
    result += '**表结构：**\n\n'
    result += '| 字段名 | 数据类型 | 允许空值 | 默认值 | 说明 |\n'
    result += '|--------|----------|----------|--------|------|\n'
    
    columns.forEach(col => {
      const name = col.COLUMN_NAME
      const type = col.DATA_TYPE
      const nullable = col.IS_NULLABLE === 'YES' ? '是' : '否'
      const defaultValue = col.COLUMN_DEFAULT || '无'
      const comment = col.COLUMN_COMMENT || '无'
      
      result += `| ${name} | ${type} | ${nullable} | ${defaultValue} | ${comment} |\n`
    })

    // 数据内容（前10条）
    result += '\n**数据内容（前10条）：**\n\n'
    
    if (data.length > 0) {
      // 动态生成表头
      const headers = Object.keys(data[0])
      result += '| ' + headers.join(' | ') + ' |\n'
      result += '|' + headers.map(() => '--------').join('|') + '|\n'
      
      // 生成数据行
      data.forEach(row => {
        const values = headers.map(header => {
          const value = row[header]
          if (value === null) return 'NULL'
          if (typeof value === 'object') return JSON.stringify(value)
          return String(value).length > 50 ? String(value).substring(0, 50) + '...' : String(value)
        })
        result += '| ' + values.join(' | ') + ' |\n'
      })
      
      result += `\n共显示 ${data.length} 条记录。`
    } else {
      result += '暂无数据。'
    }

    return result

  } catch (error) {
    console.error('查询表结构失败:', error)
    return `查询表 "${tableName}" 失败: ${error.message}`
  }
}

// 处理创建表格请求
async function handleCreateTable(request) {
  // 这里可以根据用户的自然语言请求来解析并生成SQL
  // 目前返回一个模板，实际可以集成更智能的SQL生成
  
  return `收到创建表格请求："${request}"\n\n` +
         `🔧 **AI分析：**\n` +
         `根据您的需求，我建议创建以下表格结构：\n\n` +
         `\`\`\`sql\n` +
         `-- 示例：创建操作日志表\n` +
         `CREATE TABLE IF NOT EXISTS operation_logs (\n` +
         `  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '日志ID',\n` +
         `  user_id INT NOT NULL COMMENT '用户ID',\n` +
         `  operation_type VARCHAR(50) NOT NULL COMMENT '操作类型',\n` +
         `  table_name VARCHAR(100) COMMENT '操作表名',\n` +
         `  record_id INT COMMENT '记录ID',\n` +
         `  operation_data JSON COMMENT '操作数据',\n` +
         `  ip_address VARCHAR(45) COMMENT 'IP地址',\n` +
         `  user_agent TEXT COMMENT '用户代理',\n` +
         `  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',\n` +
         `  deleted_at TIMESTAMP NULL COMMENT '软删除时间'\n` +
         `) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作日志表';\n` +
         `\`\`\`\n\n` +
         `请确认是否执行此SQL来创建表格？`
}

// 处理添加菜单请求
async function handleAddMenu(request) {
  try {
    const { DynamicMenu } = require('../models')
    const lowerRequest = request.toLowerCase()
    
    // 简单的关键词提取
    let menuName = ''
    let tableName = ''
    
    // 提取菜单名称
    const menuMatch = lowerRequest.match(/加个?\s*(.+?)(?:菜单|管理)/)
    if (menuMatch) {
      menuName = menuMatch[1].trim()
    }
    
    // 推测对应的表名
    const tableMap = {
      '产品': 'products',
      '商品': 'products', 
      '用户': 'users',
      '客户': 'customers',
      '订单': 'orders',
      '库存': 'inventory',
      '权限': 'permissions',
      '角色': 'roles'
    }
    
    tableName = tableMap[menuName] || `${menuName.replace(/\s+/g, '_')}`
    const menuKey = `${menuName.replace(/\s+/g, '_')}_management`
    
    // 检查表是否存在
    const tableExists = await checkTableExists(tableName)
    if (!tableExists) {
      return `## ❌ 菜单添加失败\n\n数据库中不存在表 "${tableName}"，请先创建对应的数据表。`
    }
    
    // 检查菜单是否已存在
    const existingMenu = await DynamicMenu.findOne({ where: { key: menuKey } })
    if (existingMenu) {
      return `## ⚠️ 菜单已存在\n\n"${menuName}管理"菜单已经存在于系统中。`
    }
    
    // 创建新菜单
    await DynamicMenu.create({
      name: `${menuName}管理`,
      key: menuKey,
      icon: 'TableCellsIcon',
      table_name: tableName,
      description: `${menuName}信息管理`,
      permissions: ['read', 'create', 'update', 'delete'],
      sort_order: 100,
      status: 'active'
    })
    
    return `
## ✅ 菜单添加成功

🎯 **菜单名称**: ${menuName}管理
📋 **对应表名**: ${tableName}
🔑 **菜单标识**: ${menuKey}

### 菜单功能
- 查看${menuName}列表
- 添加新${menuName}
- 编辑${menuName}信息  
- 删除${menuName}

**已动态添加到系统菜单中，刷新页面即可看到新菜单！**
    `
  } catch (error) {
    console.error('添加菜单失败:', error)
    return `## ❌ 添加菜单失败\n\n错误信息: ${error.message}`
  }
}

// 处理删除菜单请求  
async function handleRemoveMenu(request) {
  try {
    const { DynamicMenu } = require('../models')
    const lowerRequest = request.toLowerCase()
    
    // 提取要删除的菜单名称
    let menuName = ''
    const removeMatch = lowerRequest.match(/(?:去掉|删除)\s*(.+?)(?:菜单|管理)/)
    if (removeMatch) {
      menuName = removeMatch[1].trim()
    }
    
    // 查找菜单
    const menu = await DynamicMenu.findOne({ 
      where: { 
        name: { [require('sequelize').Op.like]: `%${menuName}%` },
        status: 'active'
      }
    })
    
    if (!menu) {
      return `## ❌ 菜单不存在\n\n未找到名称包含"${menuName}"的菜单。`
    }
    
    // 软删除菜单
    await menu.update({ status: 'inactive' })
    
    return `
## ✅ 菜单删除成功

🗑️ **已删除菜单**: ${menu.name}
📋 **对应表名**: ${menu.table_name}

菜单"${menu.name}"已成功从系统中移除。
**刷新页面后将不再显示该菜单。**
    `
  } catch (error) {
    console.error('删除菜单失败:', error)
    return `## ❌ 删除菜单失败\n\n错误信息: ${error.message}`
  }
}

// 辅助函数：检测SQL类型
function detectSQLType(sqlStatement) {
  const sql = sqlStatement.trim().toUpperCase()
  if (sql.startsWith('SELECT')) return 'SELECT'
  if (sql.startsWith('INSERT')) return 'INSERT'
  if (sql.startsWith('UPDATE')) return 'UPDATE'
  if (sql.startsWith('DELETE')) return 'DELETE'
  if (sql.startsWith('CREATE')) return 'CREATE'
  if (sql.startsWith('ALTER')) return 'ALTER'
  if (sql.startsWith('DROP')) return 'DROP'
  return 'OTHER'
}

// 辅助函数：评估风险等级
function assessRiskLevel(sqlStatement, sqlType) {
  const sql = sqlStatement.toUpperCase()
  
  // 高风险操作
  if (sqlType === 'DROP' || sql.includes('DROP TABLE') || sql.includes('TRUNCATE')) {
    return 'critical'
  }
  
  if (sqlType === 'DELETE' && !sql.includes('WHERE')) {
    return 'critical'
  }
  
  if (sqlType === 'UPDATE' && !sql.includes('WHERE')) {
    return 'high'
  }
  
  // 中风险操作
  if (['CREATE', 'ALTER', 'DELETE', 'UPDATE'].includes(sqlType)) {
    return 'medium'
  }
  
  // 低风险操作
  if (sqlType === 'SELECT') {
    return 'low'
  }
  
  return 'medium'
}

// 从AI响应中提取SQL语句
function extractSQLFromResponse(aiResponse) {
  const sqlStatements = []
  
  // 只匹配完整的SQL代码块，避免提取不完整的SQL片段
  const sqlPatterns = [
    /```sql\s*([\s\S]*?)\s*```/g,        // ```sql ... ```
  ]
  
  for (const pattern of sqlPatterns) {
    let match
    while ((match = pattern.exec(aiResponse)) !== null) {
      let sql = match[1].trim()
      // 清理SQL：移除注释和多余的换行
      sql = sql.replace(/--.*$/gm, '').replace(/\n\s*\n/g, '\n').trim()
      
      // 确保SQL语句完整（包含分号或者是完整的语句）
      if (sql && (sql.includes(';') || sql.toUpperCase().match(/^(SELECT|UPDATE|INSERT|DELETE|CREATE|ALTER|DROP)\s+/)) && 
          !sqlStatements.some(s => s.content === sql)) {
        const sqlType = detectSQLType(sql)
        sqlStatements.push({
          content: sql,
          type: sqlType,
          info: getSQLTypeDescription(sqlType),
          riskLevel: assessRiskLevel(sql, sqlType)
        })
      }
    }
  }
  
  return {
    hasSql: sqlStatements.length > 0,
    statements: sqlStatements
  }
}

// 获取SQL类型描述
function getSQLTypeDescription(sqlType) {
  const descriptions = {
    'SELECT': '查询语句 - 用于检索数据',
    'INSERT': '插入语句 - 用于添加新数据',
    'UPDATE': '更新语句 - 用于修改现有数据',
    'DELETE': '删除语句 - 用于删除数据',
    'CREATE': '创建语句 - 用于创建表或其他对象',
    'DROP': '删除语句 - 用于删除表或其他对象',
    'ALTER': '修改语句 - 用于修改表结构',
    'OTHER': 'SQL语句'
  }
  return descriptions[sqlType] || 'SQL语句'
}

// 执行菜单控制SQL
async function executeMenuControlSQL(aiResponse, userId, sessionId, userQuery) {
  let sqlLogId = null
  const startTime = Date.now()
  
  try {
        // 提取SQL语句
        const sqlStatements = []
        
        // 匹配各种SQL格式
        const sqlPatterns = [
          /```sql\n([\s\S]*?)\n```/g,
          /`sql\n([\s\S]*?)\n`/g,
          /(SELECT[\s\S]*?;)/gi,
          /(UPDATE[\s\S]*?END;)/gi  // 匹配完整的UPDATE CASE语句
        ]
        
        for (const pattern of sqlPatterns) {
          let match
          while ((match = pattern.exec(aiResponse)) !== null) {
            let sql = match[1].trim()
            // 清理SQL：移除注释和多余的换行
            sql = sql.replace(/--.*$/gm, '').replace(/\n\s*\n/g, '\n').trim()
            if (sql && !sqlStatements.includes(sql)) {
              sqlStatements.push(sql)
            }
          }
        }
    
    if (sqlStatements.length === 0) {
      console.log('未找到SQL语句')
      return null
    }
    
    console.log(' 提取到SQL语句:', sqlStatements)
    
    let executionResults = []
    
    for (const sqlQuery of sqlStatements) {
      const sqlType = detectSQLType(sqlQuery)
      const riskLevel = assessRiskLevel(sqlQuery, sqlType)
      
      // 创建SQL日志记录
      if (userId) {
        const sqlLog = await AISQLLog.create({
          user_id: userId,
          session_id: sessionId,
          user_query: userQuery,
          sql_statement: sqlQuery,
          sql_type: sqlType,
          target_table: 'dynamic_menus',
          risk_level: riskLevel,
          execution_status: 'pending',
          ai_model: 'deepseek-chat'
        })
        sqlLogId = sqlLog.id
      }
      
      // 只允许对dynamic_menus表进行安全操作
      if (!sqlQuery.toLowerCase().includes('dynamic_menus')) {
        throw new Error('只允许操作dynamic_menus表')
      }
      
      // 修复SQL中的MySQL保留关键字问题
      let fixedSqlQuery = sqlQuery
      
      // 如果SQL中还没有反引号包围的key，则添加反引号
      if (!fixedSqlQuery.includes('`key`')) {
        fixedSqlQuery = fixedSqlQuery.replace(/\bkey\b/g, '`key`')
      }
      
      // 确保表名有反引号
      fixedSqlQuery = fixedSqlQuery.replace(/FROM\s+dynamic_menus/gi, 'FROM `dynamic_menus`')
      fixedSqlQuery = fixedSqlQuery.replace(/UPDATE\s+dynamic_menus/gi, 'UPDATE `dynamic_menus`')
      
      // 执行SQL
      console.log(' 执行SQL:', fixedSqlQuery)
      const [results, metadata] = await sequelize.query(fixedSqlQuery)
      const executionTime = Date.now() - startTime
      
      // 更新SQL日志为成功状态
      if (sqlLogId) {
        await AISQLLog.update({
          execution_status: 'success',
          execution_time: executionTime,
          rows_affected: metadata?.affectedRows || results?.length || 0,
          execution_result: Array.isArray(results) ? results.slice(0, 5) : { affectedRows: metadata?.affectedRows },
          executed_at: new Date()
        }, { where: { id: sqlLogId } })
      }
      
      if (sqlType === 'SELECT') {
        executionResults.push({
          type: 'query',
          sql: sqlQuery,
          results: results,
          count: results.length
        })
      } else if (sqlType === 'UPDATE') {
        executionResults.push({
          type: 'update',
          sql: sqlQuery,
          affectedRows: metadata?.affectedRows || 0
        })
      }
    }
    
    // 生成简洁的执行结果报告
    let report = `## ✅ 菜单设置已完成\n\n`
    
    let totalUpdated = 0
    let currentMenus = []
    
    for (const result of executionResults) {
      if (result.type === 'query') {
        // 只显示活跃的菜单
        currentMenus = result.results.filter(row => row.status === 'active')
      } else if (result.type === 'update') {
        totalUpdated += result.affectedRows
      }
    }
    
    if (currentMenus.length > 0) {
      report += `### 📋 当前显示的菜单\n\n`
      currentMenus.forEach(menu => {
        report += `- ✅ ${menu.name}\n`
      })
      report += `\n`
    }
    
    if (totalUpdated > 0) {
      report += `### 🎉 操作成功\n\n`
      report += `已更新 ${totalUpdated} 个菜单项。**请刷新页面查看变化**\n\n`
    }
    
    console.log('✅ 菜单控制SQL执行完成')
    return report
    
  } catch (error) {
    console.error('菜单控制SQL执行失败:', error)
    
    // 更新SQL日志为失败状态
    if (sqlLogId) {
      const executionTime = Date.now() - startTime
      await AISQLLog.update({
        execution_status: 'failed',
        execution_time: executionTime,
        error_message: error.message,
        executed_at: new Date()
      }, { where: { id: sqlLogId } })
    }
    
    throw error
  }
}

// 处理SQL执行请求
async function handleExecuteSQL(request, sessionId = null, userId = null) {
  let sqlLogId = null
  const startTime = Date.now()
  
  try {
    // 从请求中提取SQL语句
    const sqlMatch = request.match(/```sql\n([\s\S]*?)\n```/) || request.match(/SQL[:：]\s*([\s\S]+)/)
    
    if (sqlMatch) {
      const sqlQuery = sqlMatch[1].trim()
      const sqlType = detectSQLType(sqlQuery)
      const riskLevel = assessRiskLevel(sqlQuery, sqlType)
      
      // 提取目标表名
      const tableMatch = sqlQuery.match(/(?:FROM|INTO|UPDATE|JOIN)\s+(\w+)/i)
      const targetTable = tableMatch ? tableMatch[1] : null
      
      // 创建SQL日志记录
      if (userId) {
        const sqlLog = await AISQLLog.create({
          user_id: userId,
          session_id: sessionId,
          user_query: request,
          sql_statement: sqlQuery,
          sql_type: sqlType,
          target_table: targetTable,
          risk_level: riskLevel,
          execution_status: 'pending',
          ai_model: 'deepseek-chat'
        })
        sqlLogId = sqlLog.id
        console.log('📝 SQL日志记录已创建:', sqlLogId)
      }
      
      // 安全检查 - 只允许SELECT查询
      if (sqlQuery.toLowerCase().startsWith('select')) {
        try {
          const [results] = await sequelize.query(sqlQuery)
          const executionTime = Date.now() - startTime
          
          // 更新日志为成功状态
          if (sqlLogId) {
            await AISQLLog.update({
              execution_status: 'success',
              execution_time: executionTime,
              rows_affected: results.length,
              execution_result: results.slice(0, 5), // 只保存前5条记录到日志
              executed_at: new Date()
            }, { where: { id: sqlLogId } })
          }
          
          if (results.length === 0) {
            return '## SQL执行结果\n\n查询结果为空。'
          }
          
          // 格式化为表格
          const columns = Object.keys(results[0])
          let table = '## ✅ SQL执行成功\n\n'
          table += `**执行时间**: ${executionTime}ms  \n`
          table += `**影响行数**: ${results.length}  \n`
          table += `**风险等级**: ${riskLevel}  \n\n`
          
          table += '| ' + columns.join(' | ') + ' |\n'
          table += '| ' + columns.map(() => '---').join(' | ') + ' |\n'
          
          results.slice(0, 20).forEach(row => {
            table += '| ' + columns.map(col => {
              const value = row[col]
              return value !== null && value !== undefined ? String(value) : ''
            }).join(' | ') + ' |\n'
          })
          
          if (results.length > 20) {
            table += `\n**注意**: 显示前20条记录，总共${results.length}条记录。`
          }
          
          return table
        } catch (sqlError) {
          const executionTime = Date.now() - startTime
          
          // 更新日志为失败状态
          if (sqlLogId) {
            await AISQLLog.update({
              execution_status: 'failed',
              execution_time: executionTime,
              error_message: sqlError.message,
              executed_at: new Date()
            }, { where: { id: sqlLogId } })
          }
          
          return `## ❌ SQL执行失败\n\n**错误信息**: ${sqlError.message}\n\n**执行时间**: ${executionTime}ms`
        }
      } else {
        // 高风险SQL，需要审批
        if (sqlLogId) {
          await AISQLLog.update({
            execution_status: 'cancelled',
            error_message: '非SELECT语句需要管理员审批',
            executed_at: new Date()
          }, { where: { id: sqlLogId } })
        }
        
        return `## ⚠️ SQL执行限制\n\n**风险等级**: ${riskLevel}\n\n为了安全起见，目前只支持SELECT查询语句。\n\n其他操作需要管理员审批后执行。\n\n**SQL日志ID**: ${sqlLogId}`
      }
    } else {
      return `## SQL语句格式错误\n\n请使用以下格式:\n\n\`\`\`sql\nSELECT * FROM table_name;\n\`\`\``
    }
  } catch (error) {
    const executionTime = Date.now() - startTime
    
    // 更新日志为失败状态
    if (sqlLogId) {
      await AISQLLog.update({
        execution_status: 'failed',
        execution_time: executionTime,
        error_message: error.message,
        executed_at: new Date()
      }, { where: { id: sqlLogId } })
    }
    
    console.error('SQL执行失败:', error)
    return `## ❌ SQL执行失败\n\n**错误信息**: ${error.message}\n\n**执行时间**: ${executionTime}ms`
  }
}

// 辅助函数：检查表是否存在
async function checkTableExists(tableName) {
  try {
    const [result] = await sequelize.query(`
      SELECT COUNT(*) as count 
      FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_SCHEMA = DATABASE() 
      AND TABLE_NAME = :tableName
    `, {
      replacements: { tableName },
      type: sequelize.QueryTypes.SELECT
    })
    return result.count > 0
  } catch (error) {
    console.error('检查表是否存在失败:', error)
    return false
  }
}


// DeepSeek API 集成类
class DeepSeekUsage {
  constructor(apiKey) {
    this.apiKey = apiKey || process.env.DEEPSEEK_API_KEY || 'sk-0b620cb2a4014b0a97c80ed6f2e98928'
    this.baseURL = 'https://api.deepseek.com'
  }

  // 查询余额和总用量
  async getBillingUsage() {
    try {
      const response = await axios.get(`${this.baseURL}/billing/usage`, {
        headers: {
          'Authorization': `Bearer ${this.apiKey}`
        }
      })
      return response.data
    } catch (error) {
      console.error('查询用量失败:', error.response?.data || error.message)
      throw error
    }
  }

  // 查询详细使用记录
  async getUsageDetails(startDate, endDate) {
    try {
      const response = await axios.get(`${this.baseURL}/billing/usage/details`, {
        headers: {
          'Authorization': `Bearer ${this.apiKey}`
        },
        params: {
          start_date: startDate,
          end_date: endDate
        }
      })
      return response.data
    } catch (error) {
      console.error('查询详细记录失败:', error.response?.data || error.message)
      throw error
    }
  }
}

// 简单测试路由
router.get('/stats', auth.checkPermission('chat:read'), async (ctx) => {
  console.log('📊 Stats route called!')
  ctx.body = {
    success: true,
    data: {
      total_stats: {
        total_sessions: 10,
        total_messages: 50,
        total_tokens: 1000,
        total_sql_queries: 25,
        success_rate: 95,
        avg_execution_time: 150,
        estimated_cost: 0.014
      },
      daily_stats: [
        {
          date: '2024-01-01',
          sql_count: 5,
          avg_execution_time: 120,
          success_count: 5,
          failed_count: 0,
          message_count: 10,
          total_tokens: 200,
          session_count: 2
        }
      ],
      date_range: {
        start_date: new Date().toISOString(),
        end_date: new Date().toISOString(),
        days: 30
      }
    },
    message: '测试统计数据'
  }
})

// 原始复杂路由备份
router.get('/stats-full', auth.checkPermission('chat:read'), async (ctx) => {
  try {
    const { days = 30 } = ctx.query
    console.log(`📊 获取${days}天的统计数据`)
    
    // 计算日期范围
    const endDate = new Date()
    const startDate = new Date()
    startDate.setDate(endDate.getDate() - parseInt(days))
    
    // 初始化DeepSeek API客户端
    const deepSeekUsage = new DeepSeekUsage()
    let realUsageData = null
    
    // 尝试获取真实的DeepSeek使用数据
    try {
      if (process.env.DEEPSEEK_API_KEY || 'sk-0b620cb2a4014b0a97c80ed6f2e98928') {
        realUsageData = await deepSeekUsage.getBillingUsage()
        console.log('✅ 成功获取DeepSeek真实使用数据:', realUsageData)
      } else {
        console.log('⚠️ 未配置DEEPSEEK_API_KEY，使用本地统计')
      }
    } catch (apiError) {
      console.log('⚠️ 无法获取DeepSeek真实数据，使用本地统计:', apiError.message)
    }
    
    // 从AI SQL日志获取统计数据（不限制用户，获取全局统计）
    const sqlStats = await AISQLLog.findAll({
      where: {
        created_at: {
          [Op.gte]: startDate,
          [Op.lte]: endDate
        }
      },
      attributes: [
        [sequelize.fn('DATE', sequelize.col('created_at')), 'date'],
        [sequelize.fn('COUNT', sequelize.col('id')), 'sql_count'],
        [sequelize.fn('AVG', sequelize.col('execution_time')), 'avg_execution_time'],
        [sequelize.fn('COUNT', sequelize.literal('CASE WHEN execution_status = "success" THEN 1 END')), 'success_count'],
        [sequelize.fn('COUNT', sequelize.literal('CASE WHEN execution_status = "failed" THEN 1 END')), 'failed_count']
      ],
      group: [sequelize.fn('DATE', sequelize.col('created_at'))],
      order: [[sequelize.fn('DATE', sequelize.col('created_at')), 'ASC']],
      raw: true
    })
    
    // 从聊天历史获取对话统计
    const { ChatHistory } = require('../models')
    const chatStats = await ChatHistory.findAll({
      where: {
        user_id,
        created_at: {
          [Op.gte]: startDate,
          [Op.lte]: endDate
        }
      },
      attributes: [
        [sequelize.fn('DATE', sequelize.col('created_at')), 'date'],
        [sequelize.fn('COUNT', sequelize.col('id')), 'message_count'],
        [sequelize.fn('SUM', sequelize.col('token_usage')), 'total_tokens'],
        [sequelize.fn('COUNT', sequelize.fn('DISTINCT', sequelize.col('session_id'))), 'session_count']
      ],
      group: [sequelize.fn('DATE', sequelize.col('created_at'))],
      order: [[sequelize.fn('DATE', sequelize.col('created_at')), 'ASC']],
      raw: true
    })
    
    // 合并统计数据
    const statsMap = new Map()
    
    // 处理SQL统计
    sqlStats.forEach(stat => {
      const date = stat.date
      if (!statsMap.has(date)) {
        statsMap.set(date, {
          date,
          sql_count: 0,
          avg_execution_time: 0,
          success_count: 0,
          failed_count: 0,
          message_count: 0,
          total_tokens: 0,
          session_count: 0
        })
      }
      const dayStats = statsMap.get(date)
      dayStats.sql_count = parseInt(stat.sql_count) || 0
      dayStats.avg_execution_time = parseFloat(stat.avg_execution_time) || 0
      dayStats.success_count = parseInt(stat.success_count) || 0
      dayStats.failed_count = parseInt(stat.failed_count) || 0
    })
    
    // 处理聊天统计
    chatStats.forEach(stat => {
      const date = stat.date
      if (!statsMap.has(date)) {
        statsMap.set(date, {
          date,
          sql_count: 0,
          avg_execution_time: 0,
          success_count: 0,
          failed_count: 0,
          message_count: 0,
          total_tokens: 0,
          session_count: 0
        })
      }
      const dayStats = statsMap.get(date)
      dayStats.message_count = parseInt(stat.message_count) || 0
      dayStats.total_tokens = parseInt(stat.total_tokens) || 0
      dayStats.session_count = parseInt(stat.session_count) || 0
    })
    
    // 转换为数组并计算总计
    const dailyStats = Array.from(statsMap.values()).sort((a, b) => new Date(a.date) - new Date(b.date))
    
    // 整合本地统计和真实DeepSeek数据
    const localTokens = dailyStats.reduce((sum, stat) => sum + stat.total_tokens, 0)
    const realTokens = realUsageData ? (realUsageData.total_usage || 0) : 0
    
    const totalStats = {
      total_sessions: dailyStats.reduce((sum, stat) => sum + stat.session_count, 0),
      total_messages: dailyStats.reduce((sum, stat) => sum + stat.message_count, 0),
      total_tokens: realTokens > 0 ? realTokens : localTokens, // 优先使用真实数据
      total_sql_queries: dailyStats.reduce((sum, stat) => sum + stat.sql_count, 0),
      success_rate: dailyStats.length > 0 ? 
        (dailyStats.reduce((sum, stat) => sum + stat.success_count, 0) / 
         Math.max(1, dailyStats.reduce((sum, stat) => sum + stat.sql_count, 0)) * 100) : 0,
      avg_execution_time: dailyStats.length > 0 ? 
        dailyStats.reduce((sum, stat) => sum + stat.avg_execution_time, 0) / dailyStats.length : 0,
      estimated_cost: realUsageData ? realUsageData.total_usage : (localTokens * 0.000014)
    }
    
    ctx.body = {
      success: true,
      data: {
        total_stats: totalStats,
        daily_stats: dailyStats,
        date_range: {
          start_date: startDate.toISOString(),
          end_date: endDate.toISOString(),
          days: parseInt(days)
        },
        // 添加真实DeepSeek账户信息
        deepseek_account: realUsageData ? {
          balance: realUsageData.balance,
          total_usage: realUsageData.total_usage,
          total_granted: realUsageData.total_granted,
          is_real_data: true
        } : {
          message: '无法获取真实账户数据，显示本地估算',
          is_real_data: false
        }
      },
      message: realUsageData ? 'DeepSeek真实使用数据获取成功' : 'DeepSeek本地统计数据获取成功'
    }
    
  } catch (error) {
    console.error('获取DeepSeek统计失败:', error)
    ctx.status = 500
    ctx.body = {
      success: false,
      message: '获取统计数据失败',
      error: error.message
    }
  }
})

// 获取模型信息和实时状态
router.get('/model-info', auth.checkPermission('chat:read'), async (ctx) => {
  try {
    // 模拟调用DeepSeek API获取模型信息
    const modelInfo = {
      model_name: DEEPSEEK_CONFIG.model,
      api_endpoint: DEEPSEEK_API_URL,
      max_tokens: DEEPSEEK_CONFIG.max_tokens,
      temperature: DEEPSEEK_CONFIG.temperature,
      status: 'active',
      version: '2024.1',
      capabilities: [
        'text-generation',
        'code-generation', 
        'data-analysis',
        'sql-generation',
        'multilingual-support'
      ],
      pricing: {
        input_tokens: 0.00000014, // 每token价格
        output_tokens: 0.00000028,
        currency: 'USD'
      },
      limits: {
        requests_per_minute: 60,
        tokens_per_minute: 600000,
        max_context_length: 32768
      }
    }
    
    // 获取最近的API调用状态
    const recentCalls = await AISQLLog.findAll({
      where: {
        ai_model: 'deepseek-chat',
        created_at: {
          [Op.gte]: new Date(Date.now() - 24 * 60 * 60 * 1000) // 最近24小时
        }
      },
      attributes: [
        'execution_status',
        'execution_time',
        'created_at',
        'error_message'
      ],
      order: [['created_at', 'DESC']],
      limit: 10,
      raw: true
    })
    
    const apiHealth = {
      status: recentCalls.length > 0 && recentCalls[0].execution_status === 'success' ? 'healthy' : 'unknown',
      last_call: recentCalls.length > 0 ? recentCalls[0].created_at : null,
      recent_errors: recentCalls.filter(call => call.execution_status === 'failed').length,
      avg_response_time: recentCalls.length > 0 ? 
        recentCalls.reduce((sum, call) => sum + (call.execution_time || 0), 0) / recentCalls.length : 0
    }
    
    ctx.body = {
      success: true,
      data: {
        model_info: modelInfo,
        api_health: apiHealth,
        recent_calls: recentCalls
      },
      message: '模型信息获取成功'
    }
    
  } catch (error) {
    console.error('获取模型信息失败:', error)
    ctx.status = 500
    ctx.body = {
      success: false,
      message: '获取模型信息失败',
      error: error.message
    }
  }
})

module.exports = router